﻿Imports System.Data.SqlClient
Public Class conexaoSQLServer
    Implements IAcessoBD

#Region "Variáveis"

    Private sStringConexao As String
    Private SQLCnn As New SqlConnection
    Private SQLDa As New SqlDataAdapter
    Public SQLCmd As New SqlCommand

#End Region

    Public Sub SetarStringConexao(ByVal sStringConexao As String) Implements IAcessoBD.setarStringConexao
        If SQLCnn.ConnectionString = "" Then
            Me.sStringConexao = sStringConexao
            SQLCnn.ConnectionString = Me.sStringConexao
        End If
    End Sub

#Region "Construtor"

    Public Sub New()
        Me.SQLCnn.ConnectionString = Me.sStringConexao
        Me.SQLCmd.Connection = Me.SQLCnn
        Me.SQLCmd.CommandType = CommandType.Text
    End Sub

#End Region

#Region "Métodos"

    Public Sub abrirConexao() Implements IAcessoBD.abrirConexao

        If Me.SQLCnn.State = ConnectionState.Closed Then
            Me.SQLCmd.Connection = Me.SQLCnn
            Me.SQLCmd.CommandType = CommandType.Text
            Me.SQLCnn.Open()
        End If

    End Sub

    Public Sub fecharConexao() Implements IAcessoBD.fecharConexao
        If Me.SQLCnn.State <> ConnectionState.Closed Then
            Me.SQLCnn.Close()
        End If
    End Sub

    Public Function executarAcao(ByVal sSQL As String, ByVal bAbrirFecharConexao As Boolean) As Integer Implements IAcessoBD.executarAcao

        Try

            If bAbrirFecharConexao Then
                Me.abrirConexao()
            End If

            Me.SQLCmd.CommandType = CommandType.Text
            Me.SQLCmd.CommandText = sSQL

            Return Me.SQLCmd.ExecuteNonQuery()

        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            If bAbrirFecharConexao Then
                Me.fecharConexao()
            End If
        End Try

    End Function

    Public Function executarConsulta(ByVal sSQL As String, ByVal bAbrirFecharConexao As Boolean) As System.Data.DataTable Implements IAcessoBD.executarConsulta

        Dim dsRetorno As New DataSet

        Try

            If bAbrirFecharConexao Then
                Me.abrirConexao()
            End If

            Me.SQLCmd.CommandType = CommandType.Text
            Me.SQLCmd.CommandText = sSQL
            Me.SQLDa.SelectCommand = Me.SQLCmd

            Me.SQLDa.Fill(dsRetorno)

            Return dsRetorno.Tables(0)

        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            If bAbrirFecharConexao Then
                Me.fecharConexao()
            End If
        End Try

    End Function

    Public Function executarConsultaCampo(ByVal sSQL As String, ByVal bAbrirFecharConexao As Boolean) As Object Implements IAcessoBD.executarConsultaCampo

        Dim objRetorno As Object

        Try

            If bAbrirFecharConexao Then
                Me.abrirConexao()
            End If

            Me.SQLCmd.CommandType = CommandType.Text
            Me.SQLCmd.CommandText = sSQL

            objRetorno = Me.SQLCmd.ExecuteScalar

            If objRetorno Is Nothing Then
                Return Nothing
            Else
                Return objRetorno
            End If

        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            If bAbrirFecharConexao Then
                Me.fecharConexao()
            End If
        End Try

    End Function

    Public Function montaStringConexao(ByVal sSenha As String, ByVal sBanco As String, ByVal sServidor As String, ByVal sUsuario As String) As String Implements IAcessoBD.montaStringConexao

        Return "Password=" & sSenha & ";User ID=" & sUsuario & ";Initial Catalog=" & sBanco & ";Data Source=" & sServidor

    End Function

    Public Function testarConexao(ByVal sStringConexao As String) As Boolean Implements IAcessoBD.testarConexao

        Try

            Me.SQLCnn.ConnectionString = sStringConexao
            Me.SQLCnn.Open()
            Return True

        Catch
            Return False
        Finally
            Me.SQLCnn.Close()
        End Try

    End Function

    Public Sub limparParametros() Implements IAcessoBD.limparParametros
        Try
            Me.SQLCmd.Parameters.Clear()
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

    Public Sub inserirParametro(ByVal sParametro As String, ByVal oValor As Object) Implements IAcessoBD.inserirParametro

        Try

            Me.SQLCmd.Parameters.AddWithValue(sParametro, oValor)

        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Sub

#End Region

End Class
